-- 一、对xsgl数据库完成下列操作要求：
USE "xsgl";
-- 1.将被全部学生都选修了的课程的总学分改为4学分。
/*不存在一个课程，存在一个同学，有同学没选
    下面的代码帮助所有不上英语的人全都上英语
    INSERT INTO cj("学号","课程号","成绩") SELECT "学号",'A001',90 FROM "xs" WHERE NOT exists(SELECT * FROM cj WHERE "xs"."学号"=cj."学号" AND cj."课程号"='A001');
    UPDATE xsgl.dbo.kc SET 学分 = 1 WHERE 课程号 LIKE N'A001
*/
UPDATE "kc"
SET "kc"."学分" = 4
    WHERE NOT EXISTS(SELECT *
                         FROM "xs" AS "xs2"
                         WHERE NOT EXISTS(SELECT *
                                              FROM "cj" AS "cj3"
                                              WHERE "cj3"."课程号" = "kc"."课程号"
                                                AND "cj3"."学号" = "xs2"."学号"))
-- 2.从学生表删除没有选课的学生。
/*INSERT INTO xsgl.dbo.xs (学号, 姓名, 性别, 出生时间, 专业, 总学分, 照片, 备注, 身份证) VALUES (N'2006030316', N'asd', N'女', N'1988-08-28 00:00:00.000', N'网络工程        ', 18, null, null, N'370213198808280000')*/
DELETE
    FROM "xs"
    WHERE (
              SELECT COUNT(DISTINCT "cj"."课程号")
                  FROM "cj"
                  WHERE "cj"."学号" = "xs"."学号"
          ) = 0
-- 3.将每个学生的平均分，总分和选课门数插入到数据库中（学号，姓名，平均分，总分，选课门数）
CREATE TABLE "aaa_5_3"
(
    "学号"   CHAR(10)  NOT NULL
        CONSTRAINT "FK_5_3_xs"
            REFERENCES "xs" PRIMARY KEY,
    "姓名"   NCHAR(10) NOT NULL,
    "平均分"  DOUBLE PRECISION,
    "总分"   DOUBLE PRECISION,
    "选课门数" SMALLINT
);
INSERT
    INTO "aaa_5_3"("学号", "姓名", "平均分", "总分", "选课门数")
        (
            SELECT "xs"."学号", "xs"."姓名", ISNULL(AVG("cj"."成绩"), 0) AS "平均分", ISNULL(SUM("cj"."成绩"), 0) AS "总分", COUNT(DISTINCT "cj"."课程号") AS "选课门数"
                FROM "xs" LEFT OUTER JOIN "cj" ON "CJ"."学号" = "xs"."学号"
                GROUP BY "xs"."学号", "xs"."姓名"
    );
-- 4.创建每门课程的平均分和选课人数的视图（课程号，课程名，平均分，人数）
CREATE VIEW "每门课程的平均分和选课人数的视图"("课程号", "课程名", "平均分", "人数") AS
SELECT "kc"."课程号", "kc"."课程名", ISNULL(AVG("cj"."成绩"), 0) AS "平均分", COUNT(DISTINCT "cj"."学号") AS "人数"
    FROM "kc" LEFT OUTER JOIN "cj" ON "cj"."课程号" = "kc"."课程号"
    GROUP BY "kc"."课程号", "kc"."课程名"
-- 5.将李强同学从学生表删除（提示应该先删除李强同学的选课记录）
ALTER TABLE "cj"
    DROP CONSTRAINT "FK_cj_xs";
ALTER TABLE "cj"
    ADD CONSTRAINT "FK_cj_xs"
        FOREIGN KEY ("学号") REFERENCES "xs"
            ON DELETE CASCADE;
DELETE
    FROM "xs"
    WHERE "xs"."姓名" = '李强';
-- 6.插入一条选课记录（具体内容自己选）
DELETE
    FROM "cj"
    WHERE "学号" = '2006030306'
      AND "课程号" = 'J002';
INSERT
    INTO "cj"("学号", "课程号", "成绩")
    VALUES
        ('2006030306', 'J002', 90);
-- 7.创建网络工程专业的学生的选课信息的视图，要求视图包含，学号，姓名，专业，课程号，课程名，成绩
CREATE VIEW "网络工程专业的学生的选课信息的视图"("学号", "姓名", "专业", "课程号", "课程名", "成绩") AS
SELECT "xs"."学号", "xs"."姓名", "xs"."专业", "kc"."课程号", "kc"."课程名", "cj"."成绩"
    FROM "xs" INNER JOIN "cj" ON "cj"."学号" = "xs"."学号"
              INNER JOIN "kc" ON "kc"."课程号" = "cj"."课程号"
    WHERE "xs"."专业" = '网络工程'
WITH CHECK OPTION
-- 8.查询网络工程专业的各科的平均成绩，要求使用第7题创建的视图进行查询
SELECT "课程号", AVG("成绩") AS "平均成绩"
    FROM "网络工程专业的学生的选课信息的视图"
    GROUP BY "课程号";
-- 9.查询被信息管理专业的学生都选修了的课程的课程号，课程名
/*不存在一个学生,是信心管理专业,但是没选这门课*/
SELECT "kc1"."课程号", "kc1"."课程名"
    FROM "kc" AS "kc1"
    WHERE NOT EXISTS(SELECT *
                         FROM "xs" AS "xs2"
                         WHERE "xs2"."专业" = '信息管理'
                           AND NOT EXISTS(SELECT *
                                              FROM "cj" AS "cj3"
                                              WHERE "cj3"."课程号" = "kc1"."课程号"
                                                AND "cj3"."学号" = "xs2"."学号"
                             ))
-- 10.显示选修课程数最多的学号及选修课程数最少的学号，姓名（使用派生表实现）
SELECT "tmp2"."学号", "tmp2"."姓名", "tmp2"."选课数"
    FROM (
             SELECT "xs"."学号", "xs"."姓名", COUNT("课程号") AS "选课数"
                 FROM "xs" LEFT OUTER JOIN "cj" ON "cj"."学号" = "xs"."学号"
                 GROUP BY "xs"."学号", "xs"."姓名"
         ) AS "tmp2",
         (
             SELECT COUNT("课程号") AS "选课数"
                 FROM "xs" LEFT OUTER JOIN "cj" ON "cj"."学号" = "xs"."学号"
                 GROUP BY "xs"."学号"
         ) AS "tmp1"
    GROUP BY "tmp2"."学号", "tmp2"."姓名", "tmp2"."选课数"
    HAVING "tmp2"."选课数" = MIN("tmp1"."选课数")
        OR "tmp2"."选课数" = MAX("tmp1"."选课数");
-- 11.查询每个学生成绩高于自己的平均成绩的学号，姓名，课程号和成绩（使用派生表实现）
SELECT "tmp1"."学号", "tmp1"."姓名", "tmp1"."课程号", "tmp1"."成绩"
    FROM (
             SELECT "xs"."学号", "xs"."姓名", "cj"."课程号", "cj"."成绩"
                 FROM "xs" INNER JOIN "cj" ON "cj"."学号" = "xs"."学号"
         ) AS "tmp1",
         (
             SELECT "xs"."学号", AVG("cj"."成绩") AS "平均成绩"
                 FROM "xs" INNER JOIN "cj" ON "cj"."学号" = "xs"."学号"
                 GROUP BY "xs"."学号"
         ) AS "tmp2"
    WHERE "tmp1"."学号" = "tmp2"."学号"
      AND "tmp1"."成绩" > "tmp2"."平均成绩"
-- 12.自己验证with check option的作用。
CREATE VIEW "网络工程专业的学生的视图" AS
SELECT *
    FROM "xs"
    WHERE "xs"."专业" = '网络工程'
WITH CHECK OPTION
INSERT
    INTO "网络工程专业的学生的视图"
        ("学号", "姓名", "性别", "出生时间", "专业", "总学分", "照片", "备注", "身份证")
    VALUES
        ('2006030316', 'asd', '女', '1987-07-07 00:00:00.000', 'asdasd        ', 0, NULL, NULL, NULL)
-- 13.创建一个网络工程系的学生基本信息的视图MA_STUDENT，在此视图的基础上，再定义一个该专业女生信息的视图，然后再删除MA_STUDENT，观察执行情况。
CREATE VIEW "MA_STUDENT" AS
SELECT *
    FROM "xs"
    WHERE "xs"."专业" = '网络工程'
WITH CHECK OPTION
CREATE VIEW "MA_女STUDENT" AS
SELECT *
    FROM "MA_STUDENT"
    WHERE "MA_STUDENT"."性别" = '女'
WITH CHECK OPTION
DROP VIEW "MA_STUDENT";
-- 14.查询和程明同龄的学生的学号和姓名以及年龄
SELECT "学号", "姓名", DATEDIFF(YY, "出生时间", GETDATE()) AS "年龄"
    FROM "xs"
    WHERE DATEDIFF(YY, "出生时间", GETDATE()) = (
                                                SELECT DATEDIFF(YY, "出生时间", GETDATE()) AS "年龄"
                                                    FROM "xs"
                                                    WHERE "xs"."姓名" = '程明'
                                            )
-- 15.查询没有被网络工程全部的学生都选修的课程的课程号和课程名（可以被网络工程专业的部分学生选修）
/*对于一节课,存在一个网络工程的学生,但是每选这个课*/
SELECT "kc1"."课程号", "kc1"."课程名"
    FROM "kc" AS "kc1"
    WHERE EXISTS(SELECT *
                     FROM "xs" AS "xs2"
                     WHERE "xs2"."专业" = '网络工程'
                       AND NOT EXISTS(SELECT *
                                          FROM "cj" AS "cj3"
                                          WHERE "cj3"."学号" = "xs2"."学号"
                                            AND "cj3"."课程号" = "kc1"."课程号"))
-- 16.查询没有选修数据结构，操作系统和英语三门课的学生的学号，姓名，课程号，课程名和成绩
SELECT "xs"."学号", "xs"."姓名", "kc"."课程号", "kc"."课程名", "cj"."成绩"
    FROM "xs" INNER JOIN "cj" ON "cj"."学号" = "xs"."学号"
              INNER JOIN "kc" ON "kc"."课程号" = "cj"."课程号"
    WHERE "cj"."学号" NOT IN (
                               SELECT "学号"
                                   FROM "cj" INNER JOIN "kc" ON "kc"."课程号" = "cj"."课程号" AND "kc"."课程名" IN ('数据结构', '操作系统', '英语')
                           )
-- 17.将没有选课的学生的总学分设置为0
UPDATE "xs"
SET "总学分"=0
    WHERE (
              SELECT COUNT("cj"."课程号")
                  FROM "xs" AS "xs2" LEFT OUTER JOIN "cj" ON "cj"."学号" = "xs2"."学号"
                  WHERE "xs"."学号" = "xs2"."学号"
          ) = 0
-- 二、使用Northwind数据库完成下列操作
USE "Northwind";
-- 1. 将员工lastname是: Peacock处理的订单中购买数量超过50的商品折扣改为七折
UPDATE "Order Details"
SET "Discount"=0.3
    WHERE "Quantity" > 50
      AND "OrderID" IN (
                           SELECT "OrderID"
                               FROM "Orders" INNER JOIN "Employees" ON "Orders"."EmployeeID" = "Employees"."EmployeeID"
                               WHERE "Employees"."LastName" = 'Peacock'
                       )
-- 2. 删除lastname是: Peacock处理的所有订单
DELETE
    FROM "Order Details"
    WHERE "OrderID" IN (
                           SELECT "OrderID"
                               FROM "Orders" INNER JOIN "Employees" ON "Orders"."EmployeeID" = "Employees"."EmployeeID"
                               WHERE "Employees"."LastName" = 'Peacock'
                       )
-- 3. 将每个订单的订单编号，顾客编号，产品总数量，总金额插入到数据库中
CREATE TABLE "aaa_5_3_2"
(
    "订单编号"  INT
        CONSTRAINT "PK_aaa_5_3_2"
            PRIMARY KEY,
    "顾客编号"  NCHAR(5)
        CONSTRAINT "FK_aaa_5_3_2_Customers"
            REFERENCES "Customers",
    "产品总数量" INT,
    "总金额"   DOUBLE PRECISION,
);
INSERT
    INTO "aaa_5_3_2"("订单编号", "顾客编号", "产品总数量", "总金额")
SELECT "Orders"."OrderID"                                                                                          AS "订单编号",
       "Orders"."CustomerID"                                                                                       AS "顾客编号",
       ISNULL(SUM("Order Details"."Quantity"), 0)                                                                  AS "产品总数量",
       ISNULL(SUM("Order Details"."UnitPrice" * "Order Details"."Quantity" * (1 - "Order Details"."Discount")), 0) AS "总金额"
    FROM "Orders" LEFT OUTER JOIN "Order Details" ON "Orders"."OrderID" = "Order Details"."OrderID"
    GROUP BY "Orders"."OrderID", "Orders"."CustomerID"
-- 4. 插入一个新的订单，要求该订单购买了商品编号为5,7,9的商品。（5号商品买了10个，7号买了20个，9号买了15个，都没有折扣）
DECLARE @id INT;
INSERT
    INTO "Orders"("EmployeeID")
    VALUES
        (1);
SET @id = @@IDENTITY;
INSERT
    INTO "Order Details"
        ([OrderID], [ProductID], [Quantity], [Discount])
    VALUES
        (@id, 5, 10, 0),
        (@id, 7, 20, 0),
        (@id, 9, 15, 0);
UPDATE "Order Details"
SET "Order Details"."UnitPrice"=(
                                    SELECT "UnitPrice"
                                        FROM "Products"
                                        WHERE "Products"."ProductID" = "Order Details"."ProductID"
                                )
    WHERE "OrderID" = @id;
-- 5. 将每年每个员工处理订单的数量和订单的总金额创建为视图
CREATE VIEW "每年每个员工处理订单的数量和订单的总金额创建为视图" AS
SELECT "Orders"."EmployeeID",
       YEAR("Orders"."OrderDate")                                                                                  AS "年",
       ISNULL(SUM("Order Details"."Quantity"), 0)                                                                  AS "产品总数量",
       ISNULL(SUM("Order Details"."UnitPrice" * "Order Details"."Quantity" * (1 - "Order Details"."Discount")), 0) AS "总金额"
    FROM "Orders" LEFT OUTER JOIN "Order Details" ON "Orders"."OrderID" = "Order Details"."OrderID"
    GROUP BY "Orders"."EmployeeID", YEAR("Orders"."OrderDate")
-- 6. 购买了CustomerID是‘VINET’用户所购买的全部商品的用户的CustomerID和CompanyName。
/*不存在一件商品,vinet买了,这个人没买*/
SELECT "Customers1"."CustomerID", "Customers1"."CompanyName"
    FROM "Customers" AS "Customers1"
    WHERE NOT EXISTS(SELECT *
                         FROM "Orders"                            AS "Orders2"
                                  LEFT OUTER JOIN "Order Details" AS "Order Details2" ON "Orders2"."OrderID" = "Order Details2"."OrderID"
                         WHERE "Orders2"."CustomerID" = 'vinet'
                           AND NOT EXISTS(
                                 SELECT *
                                     FROM "Orders"                            AS "Orders3"
                                              LEFT OUTER JOIN "Order Details" AS "Order Details3" ON "Orders3"."OrderID" = "Order Details3"."OrderID"
                                     WHERE "Orders3"."CustomerID" = "Customers1"."CustomerID"
                                       AND "Order Details3"."ProductID" = "Order Details2"."ProductID"
                             ))
-- 7. 将被全部顾客都购买过的商品的商品编号和商品名和单价创建为一个视图proa
/*不存在一件顾客,没购买过这个商品*/
CREATE VIEW "proa" AS
SELECT "Products1"."ProductID", "Products1"."ProductName"
    FROM "Products" AS "Products1"
    WHERE NOT EXISTS(SELECT *
                         FROM "Customers" AS "Customers2"
                         WHERE NOT EXISTS(SELECT *
                                              FROM "Orders"                            AS "Orders3"
                                                       LEFT OUTER JOIN "Order Details" AS "Order Details3" ON "Orders3"."OrderID" = "Order Details3"."OrderID"
                                              WHERE "Orders3"."CustomerID" = "Customers2"."CustomerID"
                                                AND "Order Details3"."ProductID" = "Products1"."ProductID"))
-- 8. 将被全部顾客都购买过的商品的单价加上5元
UPDATE "Products"
SET "UnitPrice"="UnitPrice" + 5
    WHERE "ProductID" IN (
                             SELECT "ProductID"
                                 FROM "proa"
                         );
-- 9. 删除订单总金额少于50元的订单明细
DELETE
    FROM "Order Details"
    WHERE "OrderID" IN (
                           SELECT "Orders"."OrderID"
                               FROM "Orders" LEFT OUTER JOIN "Order Details" ON "Orders"."OrderID" = "Order Details"."OrderID"
                               GROUP BY "Orders"."OrderID"
                               HAVING ISNULL(SUM("Order Details"."UnitPrice" * "Order Details"."Quantity" * (1 - "Order Details"."Discount")), 0) < 50
                       )